﻿CREATE PROCEDURE [dbo].[History_SelectFromCurrentRangeReview]
	@RangeReviewId int
AS

declare @startDatePY DATE = 
	(
		select CASE WHEN HasPriorYear = 1 THEN DATEADD(Month, -12, FirstMonth) ELSE FirstMonth END
		from RangeReviews where Id = @RangeReviewId
	)
declare @startDate DATE = (select FirstMonth from RangeReviews where Id = @RangeReviewId)
declare @totalMonths INT = (select TotalMonths from RangeReviews where Id = @RangeReviewId)
declare @endDate DATE = DATEADD(Month, @totalMonths - 1, @startDate)
declare @totalCoverage INT = (Select Count(StoreId) from RangeReviewStores where RangeReviewId = @RangeReviewId) * @totalMonths

declare @salesTable TABLE (Title VARCHAR(200), Country VARCHAR(50), CategoryId SMALLINT, Category VARCHAR(500), Role VARCHAR(50), Universe VARCHAR(50), Date DATE, ExchangeRate DECIMAL(18, 8), RatePerf DECIMAL(18, 8), Units BIGINT, Sales MONEY, GM MONEY, PIDiff MONEY, ChargeOffs MONEY, Rebates MONEY, InventoryCost MONEY, ArticleId INT, IsDelisted BIT, IsNew BIT, Delist BIT, IsPriorYear BIT)  

declare @acumSales money = 0;

declare @rangeTitle varchar(200) = (select Title from RangeReviews where Id = @RangeReviewId)
declare @countryId int = (select CountryId from RangeReviews where Id = @RangeReviewId)

INSERT INTO @salesTable (Title, Country, CategoryId, Category, cr.Role, cu.Universe, Date, ExchangeRate, RatePerf, Units, Sales, GM, PIDiff, ChargeOffs, Rebates, InventoryCost, ArticleId, IsDelisted, IsNew, Delist, IsPriorYear)
SELECT
	rr.Title
	, co.Name
	, cat.Id AS CategoryId
	, cat.Name AS Category
	, cr.Role
	, cu.Universe
	, sales.Date
	, ex.Rate
	, ex.ForexRate	
	, CASE WHEN umc.Id IS NOT NULL THEN SUM(Units / umc.Value) ELSE SUM(Units) END AS Units
	, CASE WHEN co.IsVATIncluded = 0 THEN SUM(Sales)  ELSE SUM(Sales / (1 + ar.TaxRate)) END AS Sales	
	, CASE WHEN am.Margin is not null THEN SUM(Sales) * am.Margin 
		   WHEN scm.Margin is not null THEN SUM(Sales) * scm.Margin 
		   WHEN cm.Margin is not null THEN SUM(Sales) * cm.Margin 
		   ELSE SUM(GM)END AS GM					
	, SUM(PIDiff) as PIDiff
	, SUM(ChargeOffs) as ChargeOffs
	, SUM(Rebates) as Rebates
	, SUM(inv.Cost) as InventoryCost
	, ar.Id
	, ar.IsDelisted
	, ar.IsNew
	, ar.Delist
	, CASE 
		WHEN sales.Date BETWEEN @startDatePY AND DATEADD(MONTH, @totalMonths - 1, @startDatePY) THEN 1
		WHEN sales.Date BETWEEN @startDate AND @endDate THEN 0
		ELSE NULL
	 END AS IsPriorYear
FROM ArticleSales sales	
	INNER JOIN Articles ar on sales.ArticleId = ar.Id and ar.RangeReviewId = @RangeReviewId and ar.IsDeletedPerformance = 0	
	INNER JOIN Categories cat on ar.CategoryId = cat.Id
	INNER JOIN RangeReviews rr on ar.RangeReviewId = rr.Id
	INNER JOIN Countries co on co.Id = rr.CountryId
	INNER JOIN Currencies cur on rr.CountryId = cur.CountryId	
	LEFT JOIN CategoryRoles cr on cr.Id = cat.CategoryRoleId
	LEFT JOIN CategoryUniverse cu on cu.Id = cat.CategoryUniverseId
	LEFT JOIN CurrencyExchangeRates ex on ex.Date = sales.Date and ex.SourceCurrencyId = cur.Id and ex.TargetCurrencyId = rr.TargetCurrencyId
	LEFT JOIN UnitOfMeasureConvertions umc on ar.UnitOfMeasureConvertionId = umc.Id and ar.UOM = umc.SourceUnit
	LEFT JOIN TheoreticalMargins am  on am.CountryId = rr.CountryId AND  am.ArticleId = ar.Id  
	LEFT JOIN TheoreticalMargins scm on scm.CountryId = rr.CountryId AND (scm.ArticleId is null AND scm.CategoryId = cat.Id) 
	LEFT JOIN TheoreticalMargins cm  on cm.CountryId = rr.CountryId AND (cm.ArticleId is null AND cm.CategoryId <> cat.Id AND cm.CategoryId = cat.ParentId)
	LEFT JOIN Inventories inv on inv.ArticleId = sales.ArticleId and inv.Date = sales.Date and inv.StoreId = sales.StoreId	
WHERE
	(sales.Date BETWEEN @startDatePY AND @endDate)
AND sales.Confirmed = 1		
AND sales.StoreId in (select StoreId from RangeReviewStores where RangeReviewId = @RangeReviewId)
GROUP BY rr.Title, cat.Id, cat.Name, cat.ParentId, ar.Id, ar.Name, ar.TaxRate, am.Margin, scm.Margin, cm.Margin, umc.Value, umc.TargetUnit, umc.Id, co.IsVATIncluded, sales.Date, ex.Rate, ex.ForexRate, ar.IsDelisted, ar.IsNew, ar.Delist, cr.Role, cu.Universe, co.Name, cu.Universe, cr.Role
OPTION(RECOMPILE);


SELECT
	  s.CategoryId
	, Category
	, s.Universe
	, s.Role
	
	, SUM(case when s.IsPriorYear = 0 then s.Units end) / CAST(@totalCoverage AS MONEY) as AverageVolume
	, SUM(case when s.IsPriorYear = 0 then s.Sales end * isnull(s.RatePerf, s.ExchangeRate)) / @totalCoverage as AverageSales
	, SUM(case when s.IsPriorYear = 0 then s.GM end * isnull(s.RatePerf, s.ExchangeRate)) / @totalCoverage as AverageGIM
	, 0 as AverageGIMP
	
	, SUM(case when s.IsPriorYear = 0 and s.IsDelisted = 1 then s.Units end) / CAST(@totalCoverage AS MONEY) as AverageDelistedVolume
	, SUM(case when s.IsPriorYear = 0 and s.IsDelisted = 1 then s.Sales end * isnull(s.RatePerf, s.ExchangeRate)) / @totalCoverage as AverageDelistedSales
	, SUM(case when s.IsPriorYear = 0 and s.IsDelisted = 1 then s.GM end * isnull(s.RatePerf, s.ExchangeRate)) / @totalCoverage as AverageDelistedGIM
		
	, SUM(case when s.IsPriorYear = 0 then s.Units end) as SS_VolumeCY
	, SUM(case when s.IsPriorYear = 0 then s.Sales end * isnull(s.RatePerf, s.ExchangeRate)) as SS_SalesCY
	, SUM(case when s.IsPriorYear = 0 then s.GM end * isnull(s.RatePerf, s.ExchangeRate)) as SS_GIMCY
	, SUM(case when s.IsPriorYear = 0 then PIDiff end) / SUM(s.Sales) as SS_PIDiffCY
	, SUM(case when s.IsPriorYear = 0 then ChargeOffs end) / SUM(s.Sales) as SS_ChargeOffCY
	, SUM(case when s.IsPriorYear = 0 then Rebates end)  as SS_RebatesCY
	, SUM(case when s.IsPriorYear = 0 then s.InventoryCost end * isnull(s.RatePerf, s.ExchangeRate)) / @totalCoverage as SS_InventoryCY
	
	, SUM(case when s.IsPriorYear = 1 then s.Units end) as SS_VolumePY
	, SUM(case when s.IsPriorYear = 1 then s.Sales end * isnull(s.RatePerf, s.ExchangeRate)) as SS_SalesPY
	, SUM(case when s.IsPriorYear = 1 then s.GM end * isnull(s.RatePerf, s.ExchangeRate)) as SS_GIMPY
	, SUM(case when s.IsPriorYear = 1 then PIDiff end) / SUM(s.Sales) as SS_PIDiffPY
	, SUM(case when s.IsPriorYear = 1 then ChargeOffs end) / SUM(s.Sales) as SS_ChargeOffPY
	, SUM(case when s.IsPriorYear = 1 then Rebates end)  as RebatesPY
	, SUM(case when s.IsPriorYear = 1 then s.InventoryCost end * isnull(s.RatePerf, s.ExchangeRate)) / @totalCoverage as SS_InventoryPY
	
	, Spaces.Gondola
	, Spaces.TransactionArea
	, Spaces.GrabAndGo
	, Spaces.WIC
	, Spaces.OpenCooler
	, Spaces.Freezer
	, Spaces.Forecourt
	, Spaces.OtherExhibition
	
	, COUNT(distinct case when s.IsPriorYear = 1 and s.Sales > 0 then ArticleId end) SKUsWithSales
	, COUNT(distinct case when s.IsNew = 1 then ArticleId end) NewSKUs
	, COUNT(distinct ArticleId) SKUsInSalesFiles	
	, COUNT(distinct case when s.Delist = 1 then ArticleId end) as DelistProposal
	
	,0 as Sales10
	,0 as Sales20
	,0 as Sales30
	,0 as Sales40
	,0 as Sales50
	,0 as Sales60
	,0 as Sales70
	,0 as Sales80
	,0 as Sales90
	,0 as Sales100
	
	,0 as Bottom5
	,0 as HurdleRate
	
	,1 as ActiveCategories
	,1 as CategoriesWithInvAnalysis
	
	, s.Country as Country
	, s.Title as Range
	
From @salesTable s
LEFT JOIN 
(
	SELECT
		ar.CategoryId
		, SUM(CASE WHEN pl.Name = 'Forecourt' THEN (ar.Width * ar.Facings) END) / 100 AS Forecourt
		, SUM(CASE WHEN pl.Name = 'Freezer' THEN (ar.Width * ar.Facings) END) / 100 AS Freezer
		, SUM(CASE WHEN pl.Name = 'Gondola' THEN (ar.Width * ar.Facings) END) / 100 AS Gondola
		, SUM(CASE WHEN pl.Name = 'Grab&Go' THEN (ar.Width * ar.Facings) END) / 100 AS GrabAndGo
		, SUM(CASE WHEN pl.Name = 'Other Exhibition' THEN (ar.Width * ar.Facings) END) / 100 AS OtherExhibition
		, SUM(CASE WHEN pl.Name = 'Transaction Area' THEN (ar.Width * ar.Facings) END) / 100 AS TransactionArea
		, SUM(CASE WHEN pl.Name = 'WIC' THEN (ar.Width * ar.Facings) END) / 100 AS WIC
		, SUM(CASE WHEN pl.Name = 'Open Cooler' THEN (ar.Width * ar.Facings) END) / 100 AS OpenCooler
	FROM Spaces sp
	INNER JOIN Articles ar on ar.Id = sp.Id
	INNER JOIN Planograms pla on pla.Id = sp.PlanogramId
	INNER JOIN PlanogramLocations pl on pl.Id = pla.PlanogramLocationId
	GROUP BY ar.CategoryId
) as Spaces on Spaces.CategoryId = s.CategoryId
Where s.IsPriorYear IS NOT NULL
Group by
	Title
	, s.CategoryId
	, s.Category
	, s.Universe
	, s.Role
	, s.Country
	, Spaces.Gondola
	, Spaces.TransactionArea
	, Spaces.GrabAndGo
	, Spaces.WIC
	, Spaces.OpenCooler
	, Spaces.Freezer
	, Spaces.Forecourt
	, Spaces.OtherExhibition
OPTION(RECOMPILE);